package com.xm.dao.background;

import com.xm.entity.Administrator;
import com.xm.entity.AdministratorInformation;
import com.xm.entity.Departments;
import com.xm.entity.Page;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * 账号信息表Dao
 *
 * @author 康文程
 * @date 2023-12-15
 */
public class AdministratorInformationDao extends BaseUtil {
    /**
     * 查询拥有该权限的员工名称
     *
     * @return 员工列表
     */
    public List<AdministratorInformation> findName(String requiredPermissions) {
        // SQL语句
        @Language("SQL")
        String sql = "SELECT ai.name,d.department_name,a.permissions FROM administrator_information ai " +
                "left join administrator a on ai.administrator_id = a.administrator_id " +
                "left join departments d on ai.department_id = d.department_id " +
                "where a.permissions like '%" + requiredPermissions + "%' or a.permissions like '%a%'";
        // 执行查询操作order by date_entry desc
        ResultSet rs = null;
        List<AdministratorInformation> administratorInformationList = new ArrayList<>();
        try {
            rs = executeQuery(sql);
            // 遍历结果集
            while (rs.next()) {
                AdministratorInformation administratorInformation = new AdministratorInformation();
                administratorInformation.setName(rs.getString("name"));

                administratorInformation.setDepartments(new Departments(rs.getString("department_name")));
                administratorInformation.setAdministrator(new Administrator(rs.getString("permissions")));
                administratorInformationList.add(administratorInformation);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rs);
        }
        return administratorInformationList;
    }

    /**
     * 添加或修改员工
     * 如果传了getInformationId则修改没有则新增
     *
     * @param administratorInformation 员工实体类
     * @throws SqlErrorException SQL执行异常
     */
    public boolean addAdministratorInformation(AdministratorInformation administratorInformation) throws SqlErrorException {
        if (administratorInformation.getInformationId() == null) {
            // 添加账号
            @Language("sql")
            String sql;
            sql = "INSERT INTO administrator_information " +
                    "(name, phone, mailbox,spare_phone,home_address," +
                    "contact,signature,contact_phone,sex,avatar," +
                    "administrator_id,date_birth,department_id) " +
                    "VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?,?)";
            int id = executeUpdate(sql,administratorInformation.getName(),
                    administratorInformation.getPhone(),
                    administratorInformation.getMailbox(),
                    administratorInformation.getSparePhone(),
                    administratorInformation.getHomeAddress(),
                    administratorInformation.getContact(),
                    administratorInformation.getSignature(),
                    administratorInformation.getContactPhone(),
                    administratorInformation.getSex(),
                    administratorInformation.getAvatar(),
                    administratorInformation.getAdministratorId(),
                    administratorInformation.getDateBirth(),
                    administratorInformation.getDepartmentId());
            return id != 0;
        } else {
            // 修改账号
            @Language("sql")
            String sql = "UPDATE administrator_information SET " +
                    "name = ?, phone = ?, mailbox = ?,spare_phone = ?," +
                    "home_address = ?,contact = ?,signature = ?," +
                    "contact_phone = ?,sex = ?,avatar = ?," +
                    "administrator_id = ?,date_birth = ?,department_id = ? " +
                    "WHERE information_id = ?";
            int id = executeUpdate(sql,administratorInformation.getName(),
                    administratorInformation.getPhone(),
                    administratorInformation.getMailbox(),
                    administratorInformation.getSparePhone(),
                    administratorInformation.getHomeAddress(),
                    administratorInformation.getContact(),
                    administratorInformation.getSignature(),
                    administratorInformation.getContactPhone(),
                    administratorInformation.getSex(),
                    administratorInformation.getAvatar(),
                    administratorInformation.getAdministratorId(),
                    administratorInformation.getDateBirth(),
                    administratorInformation.getDepartmentId(),administratorInformation.getInformationId());
            return id != 0;
        }

    }

    /**
     * 查询全部员工信息
     *
     * @return 员工列表
     */
    public List<AdministratorInformation> findAll() {
        // SQL语句
        @Language("sql")
        String sql = "select *,a.administrator_state from administrator_information ai " +
                "left join administrator a on ai.administrator_id = a.administrator_id ";
        // 执行查询操作order by date_entry desc
        ResultSet rs = null;
        List<AdministratorInformation> administratorInformationList = new ArrayList<>();
        try {
            rs = executeQuery(sql);
            // 遍历结果集
            while (rs.next()) {
                AdministratorInformation administratorInformation = new AdministratorInformation();
                administratorInformation.setInformationId(rs.getInt("information_id"));
                administratorInformation.setName(rs.getString("name"));
                administratorInformation.setPhone(rs.getString("phone"));
                administratorInformation.setSparePhone(rs.getString("spare_phone"));
                administratorInformation.setHomeAddress(rs.getString("home_address"));
                administratorInformation.setContact(rs.getString("contact"));
                administratorInformation.setSignature(rs.getString("signature"));
                administratorInformation.setContactPhone(rs.getString("contact_phone"));
                administratorInformation.setSex(rs.getInt("sex"));
                administratorInformation.setAvatar(rs.getString("avatar"));
                administratorInformation.setAdministratorId(rs.getString("administrator_id"));
                administratorInformation.setDateBirth(rs.getDate("date_birth"));
                administratorInformation.setDepartmentId(rs.getInt("department_id"));
                administratorInformation.setDateEntry(rs.getDate("date_entry"));

                administratorInformation.setAdministrator(new Administrator(rs.getInt("administrator_state")));
                administratorInformationList.add(administratorInformation);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rs);
        }
        return administratorInformationList;
    }

    /**
     * 分页查询
     *
     * @param pageNumber 分页页数
     * @param pageSize   每页数量
     * @return page<对象>
     */
    public Page<AdministratorInformation> pagination(int pageNumber, int pageSize, String employeeMc, String departmentId, String administratorState, String dateEntry) {
        Page<AdministratorInformation> page = new Page<>();
        List<AdministratorInformation> list = new ArrayList<>();
        @Language("sql")
        String countSql = "SELECT COUNT(*) FROM administrator_information";
        @Language("sql")
        String dataSql = "SELECT *,a.administrator_state FROM administrator_information ai left join administrator a " +
                "on ai.administrator_id = a.administrator_id where 1=1";
        if (employeeMc != null) {
            if (isNumeric(employeeMc)) {
                dataSql += " and information_id like '%" + employeeMc + "%'";
            } else {
                dataSql += " and name like '%" + employeeMc + "%'";
            }
        }
        if (departmentId != null && administratorState != null) {
            dataSql += " and " + departmentId + "=department_id and " + administratorState + "=administrator_state";
        }
        String data = "0";

        String order = " order by date_entry desc";
        if (dateEntry != null) {
            if (data.equals(dateEntry)) {
                order = " order by date_entry desc";
            } else {
                order = " order by date_entry asc";
            }
        }
        dataSql += order;

        dataSql += " LIMIT ?, ?";

        ResultSet countRs = null;
        ResultSet dataRs = null;
        try {
            // 查询总数
            countRs = executeQuery(countSql);
            if (countRs.next()) {
                int count = countRs.getInt(1);
                page.setCount(count);
                page.setTotalPages(pageSize);
            }

            // 分页查询数据
            int offset = (pageNumber - 1) * pageSize;
            dataRs = executeQuery(dataSql, offset, pageSize);
            while (dataRs.next()) {
                AdministratorInformation administratorInformation = extractAdministratorInformationFromResultSet(dataRs);
                list.add(administratorInformation);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(countRs);
            close(dataRs);
        }
        page.setData(list);
        page.setPageNumber(pageNumber);
        page.setPageSize(pageSize);
        return page;
    }

    /**
     * 查询的参数
     *
     * @param rs 查询结果集
     * @return 对象
     * @throws SQLException 抛出错误
     */
    private AdministratorInformation extractAdministratorInformationFromResultSet(ResultSet rs) throws SQLException {
        AdministratorInformation administratorInformation = new AdministratorInformation();
        administratorInformation.setInformationId(rs.getInt("information_id"));
        administratorInformation.setName(rs.getString("name"));
        administratorInformation.setPhone(rs.getString("phone"));
        administratorInformation.setMailbox(rs.getString("mailbox"));
        administratorInformation.setSparePhone(rs.getString("spare_phone"));
        administratorInformation.setHomeAddress(rs.getString("home_address"));
        administratorInformation.setContact(rs.getString("contact"));
        administratorInformation.setSignature(rs.getString("signature"));
        administratorInformation.setContactPhone(rs.getString("contact_phone"));
        administratorInformation.setSex(rs.getInt("sex"));
        administratorInformation.setAvatar(rs.getString("avatar"));
        administratorInformation.setAdministratorId(rs.getString("administrator_id"));
        administratorInformation.setDateBirth(rs.getDate("date_birth"));
        administratorInformation.setDepartmentId(rs.getInt("department_id"));
        administratorInformation.setDateEntry(rs.getDate("date_entry"));

        administratorInformation.setAdministrator(new Administrator(rs.getInt("administrator_state")));
        return administratorInformation;
    }

    /**
     * 根据 administrator_id 查询账号信息
     *
     * @param administratorId 管理员ID
     * @return AdministratorInformation 账号信息
     */
    public AdministratorInformation queryByAdministratorId(String administratorId) {
        @Language("SQL")
        String sql = "SELECT ai.*, a.permissions FROM administrator_information ai " +
                "LEFT JOIN administrator a ON ai.administrator_id = a.administrator_id " +
                "WHERE ai.administrator_id = ?";
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, administratorId);
            if (rs.next()) {
                return mapResultSetToAdministratorInformation(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(rs);
        }
        return null;
    }

    /**
     * 将结果集映射为 AdministratorInformation 实体类
     *
     * @param rs 结果集
     * @return AdministratorInformation 账号信息
     * @throws SQLException 抛出SQL错误
     */
    private AdministratorInformation mapResultSetToAdministratorInformation(ResultSet rs) throws SQLException {
        AdministratorInformation administratorInformation = new AdministratorInformation();
        administratorInformation.setInformationId(rs.getInt("information_id"));
        administratorInformation.setName(rs.getString("name"));
        administratorInformation.setPhone(rs.getString("phone"));
        administratorInformation.setMailbox(rs.getString("mailbox"));
        administratorInformation.setSparePhone(rs.getString("spare_phone"));
        administratorInformation.setHomeAddress(rs.getString("home_address"));
        administratorInformation.setContact(rs.getString("contact"));
        administratorInformation.setSignature(rs.getString("signature"));
        administratorInformation.setContactPhone(rs.getString("contact_phone"));
        administratorInformation.setSex(rs.getInt("sex"));
        administratorInformation.setAvatar(rs.getString("avatar"));
        administratorInformation.setAdministratorId(rs.getString("administrator_id"));
        administratorInformation.setAdministrator(new Administrator(rs.getString("permissions")));
        return administratorInformation;
    }

    /**
     * 判断是不是数字
     *
     * @param str 字符串
     * @return turn 是数字，false 不是数字
     */
    public static boolean isNumeric(String str) {
        return str != null && str.matches("-?\\d+(\\.\\d+)?");
    }

}